package com.socool.sinofaith.dao;

import java.util.ArrayList;
import java.util.HashSet;

import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.socool.sinofaith.model.QuestionAnswerBean;
import com.socool.sinofaith.model.SifTaskBean;
import com.socool.sinofaith.util.AndroidSQLiteOpenHelper;
import com.socool.sinofaith.util.SinofaithUtil;

public class QuestionAnswerDao {
	private AndroidSQLiteOpenHelper dbHelper;
	private String userMobile;
	public QuestionAnswerDao(Context context) {
		dbHelper = new AndroidSQLiteOpenHelper(context);
		userMobile=getSPString(context,"UserMobile");
	}

	// 插入记录
	// YD IsMark
	public int insert(QuestionAnswerBean questionAnswerBean) {
		Log.i("SQLite", "----insert----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(
					"insert into "
							+ QuestionAnswerBean.TABLENAME
							+ " (Task_ID,SubTask_ID,Stemp_ID,Submiter,TaskQuestion_ID,Answer,FileCount,OtherAnswer,AnswerType,IsFinish,Formto,GPS,GPSTime,RemindFlag,CreateTime,ModifyTime) values(?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
					new Object[] { questionAnswerBean.getTask_ID(),
							questionAnswerBean.getSubTask_ID(),
							questionAnswerBean.getStemp_ID(),
							questionAnswerBean.getSubmiter(),
							questionAnswerBean.getTaskQuestion_ID(),
							questionAnswerBean.getAnswer(),
							questionAnswerBean.getFileCount(),
							questionAnswerBean.getOtherAnswer(),
							questionAnswerBean.getAnswerType(),
							questionAnswerBean.getIsFinish(),
							questionAnswerBean.getFormto(),
							questionAnswerBean.getGPS(),
							questionAnswerBean.getGPSTime(),
							questionAnswerBean.getRemindFlag(),
							questionAnswerBean.getCreateTime(),
							questionAnswerBean.getModifyTime() 
							});
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 删除记录
	public int delete(QuestionAnswerBean questionAnswerBean) {
		Log.i("SQLite", "----delete----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("delete from " + QuestionAnswerBean.TABLENAME
					+ " where ID = ?",
					new Object[] { questionAnswerBean.getID() });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 删除记录
	public int delete(String Task_ID, String SubTask_ID, String Stemp_ID,
			String Submiter, String first, String end) {
		Log.i("SQLite", "----delete----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			// db.execSQL("update " +
			// QuestionAnswerBean.TABLENAME+" set Answer='',FileCount='', OtherAnswer='', AnswerType='0'"
			// +
			// " where Task_ID = ? and SubTask_ID = ? and Stemp_ID in ( ? ) and Submiter = ? and ID>cast ((select ID from "
			// + QuestionAnswerBean.TABLENAME
			// +
			// " where Task_ID = ? and SubTask_ID = ? and Stemp_ID in ( ? ) and Submiter = ? and TaskQuestion_ID=? limit 1) as int) and ID<(cast ((select ID from "
			// + QuestionAnswerBean.TABLENAME
			// +
			// " where Task_ID = ? and SubTask_ID = ? and Stemp_ID in ( ? ) and Submiter = ? and TaskQuestion_ID=? limit 1) as int)+cast (? as int))",
			// new Object[] { Task_ID, SubTask_ID, Stemp_ID, Submiter, Task_ID,
			// SubTask_ID, Stemp_ID, Submiter, first, Task_ID, SubTask_ID,
			// Stemp_ID, Submiter, first, Integer.valueOf(end)-1 });
			db.execSQL(
					"update "
							+ QuestionAnswerBean.TABLENAME
							+ " set Answer='',FileCount='', OtherAnswer=''"
							+ " where Task_ID = ? and SubTask_ID = ? and Stemp_ID=? and Submiter = ? and (cast(ModifyTime as int)>? and cast(ModifyTime as int)<?)",
					new Object[] { Task_ID, SubTask_ID, Stemp_ID, Submiter,
							Integer.valueOf(first), Integer.valueOf(end) });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 根据taskId删除记录
	public int deleteByTaskId(String taskId) {
		Log.i("SQLite", "----delete----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("delete from " + QuestionAnswerBean.TABLENAME
					+ " where Task_ID = ?", new Object[] { taskId });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 更新记录
	// YD IsMark
	public int update(QuestionAnswerBean questionAnswerBean) {
		Log.i("SQLite", "----update----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(
					"update "
							+ QuestionAnswerBean.TABLENAME
							+ " set Task_ID=?, SubTask_ID=?, Stemp_ID=?, Submiter=?, TaskQuestion_ID=?, Answer=?, FileCount=?, OtherAnswer=?, AnswerType=?, IsFinish=?, Formto=?, GPS=?,GPSTime=?, RemindFlag=?, CreateTime=?, ModifyTime=? where ID=?",
					new Object[] { questionAnswerBean.getTask_ID(),
							questionAnswerBean.getSubTask_ID(),
							questionAnswerBean.getStemp_ID(),
							questionAnswerBean.getSubmiter(),
							questionAnswerBean.getTaskQuestion_ID(),
							questionAnswerBean.getAnswer(),
							questionAnswerBean.getFileCount(),
							questionAnswerBean.getOtherAnswer(),
							questionAnswerBean.getAnswerType(),
							questionAnswerBean.getIsFinish(),
							questionAnswerBean.getFormto(),
							questionAnswerBean.getGPS(),
							questionAnswerBean.getGPSTime(),
							questionAnswerBean.getRemindFlag(),
							questionAnswerBean.getCreateTime(),
							questionAnswerBean.getModifyTime(),
							questionAnswerBean.getID() });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 更新记录
	public int update(String sql) {
		Log.i("SQLite", "----update----");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL(sql, new Object[] {});
			db.setTransactionSuccessful();
		} catch (Exception e) {
			return 0;
		} finally {
			db.endTransaction();
		}
		db.close();
		return 1;
	}

	// 查询记录
	// YD IsMark
	public ArrayList<QuestionAnswerBean> query(String id) {
		Log.i("SQLite", "----query----");
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		QuestionAnswerBean questionAnswerBean;
		ArrayList<QuestionAnswerBean> list = new ArrayList<QuestionAnswerBean>();
		// 若fileId为null或""则查询所有记录
		if (id == null || id.equals("")) {
			cursor = db.rawQuery("select * from "
					+ QuestionAnswerBean.TABLENAME, null);
		} else {
			cursor = db.rawQuery("select * from "
					+ QuestionAnswerBean.TABLENAME + " where ID=?",
					new String[] { id });
		}
		while (cursor.moveToNext()) {
			questionAnswerBean = new QuestionAnswerBean();
			questionAnswerBean
					.setID(cursor.getInt(cursor.getColumnIndex("ID")));
			questionAnswerBean.setTask_ID(cursor.getString(cursor
					.getColumnIndex("Task_ID")));
			questionAnswerBean.setSubTask_ID(cursor.getString(cursor
					.getColumnIndex("SubTask_ID")));
			questionAnswerBean.setStemp_ID(cursor.getString(cursor
					.getColumnIndex("Stemp_ID")));
			questionAnswerBean.setSubmiter(cursor.getString(cursor
					.getColumnIndex("Submiter")));
			questionAnswerBean.setTaskQuestion_ID(cursor.getString(cursor
					.getColumnIndex("TaskQuestion_ID")));
			questionAnswerBean.setAnswer(cursor.getString(cursor
					.getColumnIndex("Answer")));
			questionAnswerBean.setFileCount(cursor.getString(cursor
					.getColumnIndex("FileCount")));
			questionAnswerBean.setOtherAnswer(cursor.getString(cursor
					.getColumnIndex("OtherAnswer")));
			questionAnswerBean.setAnswerType(cursor.getString(cursor
					.getColumnIndex("AnswerType")));
			questionAnswerBean.setIsFinish(cursor.getString(cursor
					.getColumnIndex("IsFinish")));
			questionAnswerBean.setFormto(cursor.getString(cursor
					.getColumnIndex("Formto")));
			questionAnswerBean.setGPS(cursor.getString(cursor.getColumnIndex("GPS")));
			questionAnswerBean.setGPSTime(cursor.getString(cursor.getColumnIndex("GPSTime")));
			questionAnswerBean.setRemindFlag(cursor.getString(cursor
					.getColumnIndex("RemindFlag")));
			questionAnswerBean.setCreateTime(cursor.getString(cursor
					.getColumnIndex("CreateTime")));
			questionAnswerBean.setModifyTime(cursor.getString(cursor
					.getColumnIndex("ModifyTime")));
			Log.i("SQLite", questionAnswerBean.toString());
			list.add(questionAnswerBean);
		}
		cursor.close();
		db.close();
		if (list.size() == 0) {
			Log.i("SQLite", "****表中无数据****");
		}
		return list;
	}

	// 自定义查询记录
	// YD IsMark
	public ArrayList<QuestionAnswerBean> queryBySql(String sql) {
		Log.i("SQLite", "----query----");
		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor;
		QuestionAnswerBean questionAnswerBean;
		ArrayList<QuestionAnswerBean> list = new ArrayList<QuestionAnswerBean>();
		cursor = db.rawQuery(sql, null);
//		 String temp="";
//				 for(int i=0;i<cursor.getColumnNames().length;i++){
//				 temp+=cursor.getColumnNames()[i];
//				
//				 }
//				 temp+="a";
//				 Log.i("TAG", temp);
		while (cursor.moveToNext()) {
			questionAnswerBean = new QuestionAnswerBean();
			questionAnswerBean
					.setID(cursor.getInt(cursor.getColumnIndex("ID")));
			questionAnswerBean.setTask_ID(cursor.getString(cursor
					.getColumnIndex("Task_ID")));
			questionAnswerBean.setSubTask_ID(cursor.getString(cursor
					.getColumnIndex("SubTask_ID")));
			questionAnswerBean.setStemp_ID(cursor.getString(cursor
					.getColumnIndex("Stemp_ID")));
			questionAnswerBean.setSubmiter(cursor.getString(cursor
					.getColumnIndex("Submiter")));
			questionAnswerBean.setTaskQuestion_ID(cursor.getString(cursor
					.getColumnIndex("TaskQuestion_ID")));
			questionAnswerBean.setAnswer(cursor.getString(cursor
					.getColumnIndex("Answer")));
			questionAnswerBean.setFileCount(cursor.getString(cursor
					.getColumnIndex("FileCount")));
			questionAnswerBean.setOtherAnswer(cursor.getString(cursor
					.getColumnIndex("OtherAnswer")));
			questionAnswerBean.setAnswerType(cursor.getString(cursor
					.getColumnIndex("AnswerType")));
			questionAnswerBean.setIsFinish(cursor.getString(cursor
					.getColumnIndex("IsFinish")));
			questionAnswerBean.setFormto(cursor.getString(cursor.getColumnIndex("Formto")));
			questionAnswerBean.setGPS(cursor.getString(cursor.getColumnIndex("GPS")));
			questionAnswerBean.setGPSTime(cursor.getString(cursor.getColumnIndex("GPSTime")));
			questionAnswerBean.setRemindFlag(cursor.getString(cursor
					.getColumnIndex("RemindFlag")));
			questionAnswerBean.setCreateTime(cursor.getString(cursor
					.getColumnIndex("CreateTime")));
			questionAnswerBean.setModifyTime(cursor.getString(cursor
					.getColumnIndex("ModifyTime")));
			if (cursor.getColumnIndex("Template_ID") != -1) {
				questionAnswerBean.setTemplate(cursor.getString(cursor
						.getColumnIndex("Template_ID")));
			}

			Log.i("SQLite", questionAnswerBean.toString());
			list.add(questionAnswerBean);
		}
		cursor.close();
		db.close();
		if (list.size() == 0) {
			Log.i("SQLite", "****表中无数据****");
		}
		return list;
	}

	public String queryTaskIdSet() {

		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		String list = "";
		String sql = "select * from " + QuestionAnswerBean.TABLENAME
				+ " where IsFinish<>2 and Submiter="
				+ userMobile;
		cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			list += cursor.getString(cursor.getColumnIndex("Task_ID")) + ",";
		}
		cursor.close();
		db.close();
		if (list.length() == 0) {
			Log.i("SQLite", "****表中无数据****");
		} else {
			list = list.substring(0, list.length() - 1);
		}
		return list;

	}

	public Long getTaskQuestionCount(String taskId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		String sql = "select  count(*) from " + QuestionAnswerBean.TABLENAME
				+ " where Task_ID=" + taskId;
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		Long count = cursor.getLong(0);
		cursor.close();

		return count;
	}

	public Long getTaskFinshedCount(String taskId) {

		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		String sql = "select  * from " + QuestionAnswerBean.TABLENAME
				+ " where Task_ID=" + taskId
				+ " and IsFinish=2 group by Stemp_ID,SubTask_ID,Task_ID";
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		Long count = (long) cursor.getCount();
		cursor.close();
		return count;
	}

	public Long getAnswerSifTaskQuestionCount(String subtaskId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		String sql = "select  count(*) from " + QuestionAnswerBean.TABLENAME
				+ " where SubTask_ID=" + subtaskId;
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		Long count = cursor.getLong(0);
		cursor.close();

		return count;
	}

	public Long getFinshiedSifTaskQuestionCount(String subtaskId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor;
		String sql = "select  count(*) from " + QuestionAnswerBean.TABLENAME
				+ " where SubTask_ID=" + subtaskId + " and IsFinish=2";
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		Long count = cursor.getLong(0);
		cursor.close();

		return count;
	}

	
	
	private String getSPString(Context context,String str){
		SharedPreferences sp=context.getSharedPreferences("login", Context.MODE_PRIVATE);
		return sp.getString("UserMobile", "");
	}
}
